How to concatenate text from multiple rows into a single text string in SQL Server

Question

Ashutosh Kumar Verma · Answer

You can contact multiple row values into a single text string value by using the SQL built-in function
STRING_AGG().and 
Note- STRING_AGG() function supported in SQL Server version 2017 and later
Syntax-and 
SELECT STRING_AGG(column_name, ', ') as ConcatedText FROM table_name
The STRING_AGG() function requires 2 argument(s), the first one is the
column name and the second one is a separater between values as you see in the above syntax.
and 
Example-
suppose we have a Employees table.and 
CREATE TABLE Employees
(
	EmpId INT PRIMARY KEY NOT NULL,
	EmpName NVARCHAR(50), 
	Gender NVARCHAR(20),
	Salary DECIMAL(10, 2)
)
Let's insert some values into above table,
INSERT INTO Employees
VALUES('Priya Shukla', 'Female', 70788),
('Ashutosh Verma', 'Male', 50066),
('Rani Sharma', 'Female', 57722),
('Amit Tiwari', 'Male', 81391),
('Ashu Patel', 'Male', 57659),
('Samiksha Mishra', 'Female', 15000),
('Akanksha Singh', 'Female', 15000);
As you can see in the below picture,
and 
Example-
Now, use STRING_AGG() to concat multiple row values into a single string,
SELECT STRING_AGG(EmpName, ', ') as ConcatedText FROM Employees
-- output: Priya Shukla, Ashutosh Verma, Rani Sharma, Amit Tiwari, Ashu Patel, Samiksha Mishra, Akanksha Singh
and 
Also, Read: 
How to use searching and filtering data in an SQL server?

forum

How to concatenate text from multiple rows into a single text string in SQL Server

Sandra Emily

1 Answers

Liked By